﻿using System;
using System.Collections.Generic;
using System.Text;
using Dapper;

namespace EC.Application.Impl
{
    using EC.Application.Interface;
    using EC.Entity;
    using EC.Entity.Query;
    using EC.Entity.Request.Recharge;
    using EC.Entity.Response.BalanceLog;
    using Microsoft.EntityFrameworkCore.Internal;
    using System.Linq;

    public class BalanceLogServiceImpl : BalanceLogService
    {
        #region 注入
        /// <summary>
        /// 数据接口
        /// </summary>
        private readonly IDBSession _dbSession;


        /// <summary>
        /// 数据库上下文
        /// </summary>
        /// <param name="context">数据库上下文</param>
        public BalanceLogServiceImpl(IDBSession dbSession)
        {
            _dbSession = dbSession;
        }
        #endregion

        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public PagedList<BalanceLogQueryResponse> GetPager(BalanceLogQuery query)
        {
            var currentPageIndex = 0;

            if (query.CurrentPageIndex.Value > 0)
            {
                currentPageIndex = (query.CurrentPageIndex.Value - 1) * query.PageSize.Value;
            }


            var files = "Id,(SELECT Name FROM merchant WHERE Id=b.MerchantId) AS MerchantName,OrderNo,Source,Type,Amount,BeforeBalance,AfterBalance,Remark,CreatedBy,(SELECT Account FROM user WHERE Id=b.CreatedBy) AS CreatedByName,CreatedDate";
            var tableName = "balancelog b";
            var orderby = "ORDER BY Id DESC";
            var where = $"where MerchantId in ({string.Join(", ", query.MerchantIds)})";

            #region 条件
            if (query.LogSource.HasValue)
            {
                where += string.Format(" And Source ={0}", query.LogSource.Value);
            }

            if (!string.IsNullOrEmpty(query.StartTime) && !string.IsNullOrEmpty(query.EndTime))
            {
                where += string.Format(" And b.CreatedDate between '{0}' and '{1}'", query.StartTime, query.EndTime);
            }
            #endregion

            var sb = new StringBuilder();

            sb.AppendFormat("SELECT COUNT(1) FROM {0} {1};", tableName, where);
            sb.AppendFormat("SELECT {0} FROM {1} {2} {3} LIMIT {4},{5};", files, tableName, where, orderby, currentPageIndex, query.PageSize.Value);

            using (var multi = _dbSession.DbConnection.QueryMultiple(sb.ToString()))
            {
                var count = multi.ReadFirst<int>();

                var list = multi.Read<BalanceLogQueryResponse>();

                return new PagedList<BalanceLogQueryResponse>()
                {
                    TData = list,
                    CurrentPageIndex = query.CurrentPageIndex.Value,
                    TotalCount = count
                };
            }
        }


        /// <summary>
        /// 余额导出
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public DataGrid<BalanceLogQueryResponse> GetBalanceExportPager(BalanceLogQuery query)
        {
            var currentPageIndex = 0;

            if (query.CurrentPageIndex.Value > 0)
            {
                currentPageIndex = (query.CurrentPageIndex.Value - 1) * query.PageSize.Value;
            }


            var files = "Id,(SELECT Name FROM merchant WHERE Id=b.MerchantId) AS MerchantName,OrderNo,Source,Type,Amount,BeforeBalance,AfterBalance,Remark,CreatedBy,CreatedDate";
            var tableName = "balancelog b";
            var orderby = "ORDER BY Id DESC";
            var where = $"where MerchantId in ({string.Join(", ", query.MerchantIds)})";

            #region 条件
            if (query.LogSource.HasValue)
            {
                where += string.Format(" And Source ={0}", query.LogSource.Value);
            }

            if (!string.IsNullOrEmpty(query.StartTime) && !string.IsNullOrEmpty(query.EndTime))
            {
                where += string.Format(" And b.CreatedDate between '{0}' and '{1}'", query.StartTime, query.EndTime);
            }
            #endregion

            var sb = new StringBuilder();

            sb.AppendFormat("SELECT COUNT(1) FROM {0} {1};", tableName, where);
            sb.AppendFormat("SELECT {0} FROM {1} {2} {3} LIMIT {4},{5};", files, tableName, where, orderby, currentPageIndex, query.PageSize.Value);

            using (var multi = _dbSession.DbConnection.QueryMultiple(sb.ToString()))
            {
                var count = multi.ReadFirst<int>();

                var list = multi.Read<BalanceLogQueryResponse>();

                return new DataGrid<BalanceLogQueryResponse>()
                {
                    Total = count,
                    Rows = list,
                    Footer = null,
                    Success = true
                };
            }
        }


        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public PagedList<BalanceLogQueryResponse> GetSysPager(BalanceLogQuery query)
        {
            var currentPageIndex = 0;

            if (query.CurrentPageIndex.Value > 0)
            {
                currentPageIndex = (query.CurrentPageIndex.Value - 1) * query.PageSize.Value;
            }
            //MerchantName

            var files = "Id,MerchantId,(SELECT Name FROM merchant WHERE Id=b.MerchantId) AS MerchantName,OrderNo,Source,Type,Amount,BeforeBalance,AfterBalance,Remark,CreatedBy,CreatedDate";
            var tableName = "balancelog b";
            var orderby = "ORDER BY Id DESC";
            var where = $"where CreatedBy = {query.UserId}";

            #region 条件
            if (query.LogSource.HasValue)
            {
                where += string.Format(" And Source ={0}", query.LogSource.Value);
            }

            if (!string.IsNullOrEmpty(query.StartTime) && !string.IsNullOrEmpty(query.EndTime))
            {
                where += string.Format(" And d.CreatedDate between '{0}' and '{1}'", query.StartTime, query.EndTime);
            }
            #endregion

            var sb = new StringBuilder();

            sb.AppendFormat("SELECT COUNT(1) FROM {0} {1};", tableName, where);
            sb.AppendFormat("SELECT {0} FROM {1} {2} {3} LIMIT {4},{5};", files, tableName, where, orderby, currentPageIndex, query.PageSize.Value);

            using (var multi = _dbSession.DbConnection.QueryMultiple(sb.ToString()))
            {
                var count = multi.ReadFirst<int>();

                var list = multi.Read<BalanceLogQueryResponse>();

                return new PagedList<BalanceLogQueryResponse>()
                {
                    TData = list,
                    CurrentPageIndex = query.CurrentPageIndex.Value,
                    TotalCount = count
                };
            }
        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public DataGrid<RechargeQueryResponse> GetRechargeRecordPager(RechargeRecordQuery query)
        {
            var files = "r.Id,r.MerchantId,(SELECT Name FROM merchant WHERE Id=r.MerchantId) AS MerchantName,Amount,Fee,BeforeBalance,AfterBalance,Remark,u.Account as CreatedByName,r.CreatedBy,r.CreatedDate";
            var tableName = "recharge r left join user u on u.Id=r.CreatedBy ";
            var orderby = "ORDER BY Id DESC";
            var where = $"where r.MerchantId in ({string.Join(", ", query.MerchantIds)})";

            #region 条件
            if (!string.IsNullOrEmpty(query.StartTime) && !string.IsNullOrEmpty(query.EndTime))
            {
                where += string.Format(" And r.CreatedDate between '{0}' and '{1}'", query.StartTime, query.EndTime);
            }

            if (!string.IsNullOrEmpty(query.HandBy))
            {
                where += string.Format(" and u.Account ='{0}'", query.HandBy);
            }

            #endregion

            var sb = new StringBuilder();

            sb.AppendFormat("SELECT COUNT(1) FROM {0} {1};", tableName, where);
            sb.AppendFormat("SELECT {0} FROM {1} {2} {3} LIMIT {4},{5};", files, tableName, where, orderby, query.Page.Value - 1, query.Rows.Value);

            //查询汇总
            var countSql = string.Format("SELECT sum(Amount) Amount,SUM(Fee) Fee FROM {1} {2};", files, tableName, where);
            var totalAmount = new decimal(0);
            var totalFee = new decimal(0);

            var total = _dbSession.DbConnection.QueryFirst<RechargeQueryResponse>(countSql);
            if (total != null)
            {
                totalAmount = total.Amount;
                totalFee = total.Fee;
            }


            using (var multi = _dbSession.DbConnection.QueryMultiple(sb.ToString()))
            {
                var count = multi.ReadFirst<int>();

                var list = multi.Read<RechargeQueryResponse>();

                var amountSum = new decimal(0);
                var feeSum = new decimal(0);
                //需要特殊处理的列
                if (list != null && list.Count() > 0)
                {
                    foreach (var item in list)
                    {
                        amountSum += item.Amount;
                        feeSum += item.Fee;

                        item._Id = item.Id.ToString();
                        item._Amount = item.Amount.ToString("#0.00");
                        item._Fee = item.Fee.ToString("#0.00");
                        item._CreatedDate = item.CreatedDate.ToString("yyyy-MM-dd HH:mm:ss");
                    }
                }

                //定义页脚
                var footers = new List<RechargeQueryResponse>() {
                    new RechargeQueryResponse()
                    {
                        MerchantName="小计",
                        _Amount =amountSum.ToString("#0.00"),
                        _Fee = feeSum.ToString("#0.00")
                    },
                    new RechargeQueryResponse()
                    {
                        MerchantName="合计",
                        _Amount =totalAmount.ToString("#0.00"),
                        _Fee = totalFee.ToString("#0.00")
                    }
                };

                return new DataGrid<RechargeQueryResponse>()
                {
                    Total = count,
                    Rows = list,
                    Footer= footers,
                    Success = true
                };
            }
        }
    }
}
